{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[nltk_data] Downloading package stopwords to\n",
      "[nltk_data]     /home/magupta/nltk_data...\n",
      "[nltk_data]   Package stopwords is already up-to-date!\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import json\n",
    "import string\n",
    "import nltk\n",
    "from nltk.corpus import stopwords\n",
    "from bs4 import BeautifulSoup\n",
    "nltk.download('stopwords')\n",
    "from nltk.stem.snowball import SnowballStemmer\n",
    "stemmer = SnowballStemmer(\"english\")\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "answers=pd.read_csv(\"data_stackOverflow/original_data/Answers.csv\", encoding='latin-1')\n",
    "questions=pd.read_csv(\"data_stackOverflow/original_data/Questions.csv\", encoding='latin-1')\n",
    "tags=pd.read_csv(\"data_stackOverflow/original_data/Tags.csv\", encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "questions_original=pd.read_csv(\"data_stackOverflow/original_data/Questions.csv\", encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"<p>Has anyone got experience creating <strong>SQL-based ASP.NET</strong> site-map providers?</p>\\n\\n<p>I've got the default XML file <code>web.sitemap</code> working properly with my Menu and <strong>SiteMapPath</strong> controls, but I'll need a way for the users of my site to create and modify pages dynamically.</p>\\n\\n<p>I need to tie page viewing permissions into the standard <code>ASP.NET</code> membership system as well.</p>\\n\""
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_original.loc[2]['Body']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{\"ain't\": 'am not / are not',\n",
       " \"aren't\": 'are not / am not',\n",
       " \"can't\": 'cannot',\n",
       " \"can't've\": 'cannot have',\n",
       " \"'cause\": 'because',\n",
       " \"could've\": 'could have',\n",
       " \"couldn't\": 'could not',\n",
       " \"couldn't've\": 'could not have',\n",
       " \"didn't\": 'did not',\n",
       " \"doesn't\": 'does not',\n",
       " \"don't\": 'do not',\n",
       " \"hadn't\": 'had not',\n",
       " \"hadn't've\": 'had not have',\n",
       " \"hasn't\": 'has not',\n",
       " \"haven't\": 'have not',\n",
       " \"he'd\": 'he had / he would',\n",
       " \"he'd've\": 'he would have',\n",
       " \"he'll\": 'he shall / he will',\n",
       " \"he'll've\": 'he shall have / he will have',\n",
       " \"he's\": 'he has / he is',\n",
       " \"how'd\": 'how did',\n",
       " \"how'd'y\": 'how do you',\n",
       " \"how'll\": 'how will',\n",
       " \"how's\": 'how has / how is',\n",
       " \"i'd\": 'I had / I would',\n",
       " \"i'd've\": 'I would have',\n",
       " \"i'll\": 'I shall / I will',\n",
       " \"i'll've\": 'I shall have / I will have',\n",
       " \"i'm\": 'I am',\n",
       " \"i've\": 'I have',\n",
       " \"isn't\": 'is not',\n",
       " \"it'd\": 'it had / it would',\n",
       " \"it'd've\": 'it would have',\n",
       " \"it'll\": 'it shall / it will',\n",
       " \"it'll've\": 'it shall have / it will have',\n",
       " \"it's\": 'it has / it is',\n",
       " \"let's\": 'let us',\n",
       " \"ma'am\": 'madam',\n",
       " \"mayn't\": 'may not',\n",
       " \"might've\": 'might have',\n",
       " \"mightn't\": 'might not',\n",
       " \"mightn't've\": 'might not have',\n",
       " \"must've\": 'must have',\n",
       " \"mustn't\": 'must not',\n",
       " \"mustn't've\": 'must not have',\n",
       " \"needn't\": 'need not',\n",
       " \"needn't've\": 'need not have',\n",
       " \"o'clock\": 'of the clock',\n",
       " \"oughtn't\": 'ought not',\n",
       " \"oughtn't've\": 'ought not have',\n",
       " \"shan't\": 'shall not',\n",
       " \"sha'n't\": 'shall not',\n",
       " \"shan't've\": 'shall not have',\n",
       " \"she'd\": 'she had / she would',\n",
       " \"she'd've\": 'she would have',\n",
       " \"she'll\": 'she shall / she will',\n",
       " \"she'll've\": 'she shall have / she will have',\n",
       " \"she's\": 'she has / she is',\n",
       " \"should've\": 'should have',\n",
       " \"shouldn't\": 'should not',\n",
       " \"shouldn't've\": 'should not have',\n",
       " \"so've\": 'so have',\n",
       " \"so's\": 'so as / so is',\n",
       " \"that'd\": 'that would / that had',\n",
       " \"that'd've\": 'that would have',\n",
       " \"that's\": 'that has / that is',\n",
       " \"there'd\": 'there had / there would',\n",
       " \"there'd've\": 'there would have',\n",
       " \"there's\": 'there has / there is',\n",
       " \"they'd\": 'they had / they would',\n",
       " \"they'd've\": 'they would have',\n",
       " \"they'll\": 'they shall / they will',\n",
       " \"they'll've\": 'they shall have / they will have',\n",
       " \"they're\": 'they are',\n",
       " \"they've\": 'they have',\n",
       " \"to've\": 'to have',\n",
       " \"wasn't\": 'was not',\n",
       " \"we'd\": 'we had / we would',\n",
       " \"we'd've\": 'we would have',\n",
       " \"we'll\": 'we will',\n",
       " \"we'll've\": 'we will have',\n",
       " \"we're\": 'we are',\n",
       " \"we've\": 'we have',\n",
       " \"weren't\": 'were not',\n",
       " \"what'll\": 'what shall / what will',\n",
       " \"what'll've\": 'what shall have / what will have',\n",
       " \"what're\": 'what are',\n",
       " \"what's\": 'what has / what is',\n",
       " \"what've\": 'what have',\n",
       " \"when's\": 'when has / when is',\n",
       " \"when've\": 'when have',\n",
       " \"where'd\": 'where did',\n",
       " \"where's\": 'where has / where is',\n",
       " \"where've\": 'where have',\n",
       " \"who'll\": 'who shall / who will',\n",
       " \"who'll've\": 'who shall have / who will have',\n",
       " \"who's\": 'who has / who is',\n",
       " \"who've\": 'who have',\n",
       " \"why's\": 'why has / why is',\n",
       " \"why've\": 'why have',\n",
       " \"will've\": 'will have',\n",
       " \"won't\": 'will not',\n",
       " \"won't've\": 'will not have',\n",
       " \"would've\": 'would have',\n",
       " \"wouldn't\": 'would not',\n",
       " \"wouldn't've\": 'would not have',\n",
       " \"y'all\": 'you all',\n",
       " \"y'all'd\": 'you all would',\n",
       " \"y'all'd've\": 'you all would have',\n",
       " \"y'all're\": 'you all are',\n",
       " \"y'all've\": 'you all have',\n",
       " \"you'd\": 'you had / you would',\n",
       " \"you'd've\": 'you would have',\n",
       " \"you'll\": 'you shall / you will',\n",
       " \"you'll've\": 'you shall have / you will have',\n",
       " \"you're\": 'you are',\n",
       " \"you've\": 'you have'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('contractions.json') as json_file:\n",
    "    contractions = json.load(json_file)\n",
    "contractions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1264216, 7), (3750994, 2))"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions.shape,tags.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>OwnerUserId</th>\n",
       "      <th>CreationDate</th>\n",
       "      <th>ParentId</th>\n",
       "      <th>Score</th>\n",
       "      <th>Body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>92</td>\n",
       "      <td>61.0</td>\n",
       "      <td>2008-08-01T14:45:37Z</td>\n",
       "      <td>90</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;p&gt;&lt;a href=\"http://svnbook.red-bean.com/\"&gt;Vers...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>124</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2008-08-01T16:09:47Z</td>\n",
       "      <td>80</td>\n",
       "      <td>12</td>\n",
       "      <td>&lt;p&gt;I wound up using this. It is a kind of a ha...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Id  OwnerUserId          CreationDate  ParentId  Score  \\\n",
       "0   92         61.0  2008-08-01T14:45:37Z        90     13   \n",
       "1  124         26.0  2008-08-01T16:09:47Z        80     12   \n",
       "\n",
       "                                                Body  \n",
       "0  <p><a href=\"http://svnbook.red-bean.com/\">Vers...  \n",
       "1  <p>I wound up using this. It is a kind of a ha...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answers.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>OwnerUserId</th>\n",
       "      <th>CreationDate</th>\n",
       "      <th>ClosedDate</th>\n",
       "      <th>Score</th>\n",
       "      <th>Title</th>\n",
       "      <th>Body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2008-08-01T13:57:07Z</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26</td>\n",
       "      <td>SQLStatement.execute() - multiple queries in o...</td>\n",
       "      <td>&lt;p&gt;I've written a database generation script i...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>58.0</td>\n",
       "      <td>2008-08-01T14:41:24Z</td>\n",
       "      <td>2012-12-26T03:45:49Z</td>\n",
       "      <td>144</td>\n",
       "      <td>Good branching and merging tutorials for Torto...</td>\n",
       "      <td>&lt;p&gt;Are there any really good tutorials explain...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  OwnerUserId          CreationDate            ClosedDate  Score  \\\n",
       "0  80         26.0  2008-08-01T13:57:07Z                   NaN     26   \n",
       "1  90         58.0  2008-08-01T14:41:24Z  2012-12-26T03:45:49Z    144   \n",
       "\n",
       "                                               Title  \\\n",
       "0  SQLStatement.execute() - multiple queries in o...   \n",
       "1  Good branching and merging tutorials for Torto...   \n",
       "\n",
       "                                                Body  \n",
       "0  <p>I've written a database generation script i...  \n",
       "1  <p>Are there any really good tutorials explain...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>Tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>flex</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80</td>\n",
       "      <td>actionscript-3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id             Tag\n",
       "0  80            flex\n",
       "1  80  actionscript-3"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unwanted columns\n",
    "answers.drop(columns=['OwnerUserId','CreationDate','ParentId'],inplace=True)\n",
    "questions.drop(columns=['OwnerUserId','CreationDate','ClosedDate'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "answers=answers.rename(columns={'Body': 'ans_body','Score': 'ans_score'})\n",
    "questions=questions.rename(columns={'Body': 'ques_body','Score': 'ques_score','Title': 'ques_title'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(Index(['Id', 'ans_score', 'ans_body'], dtype='object'),\n",
       " Index(['Id', 'ques_score', 'ques_title', 'ques_body'], dtype='object'),\n",
       " Index(['Id', 'Tag'], dtype='object'))"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answers.columns,questions.columns,tags.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2014516, 2014516, 0)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#questions and tags dataframe have same set of Ids.\n",
    "difference_1=set(answers['Id']).difference(set(questions['Id']))\n",
    "difference_2=set(answers['Id']).difference(set(tags['Id']))\n",
    "difference_3=set(questions['Id']).difference(set(tags['Id']))\n",
    "len(difference_1),len(difference_2),len(difference_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#removes paragraph and next line symbols\n",
    "def remove_symbols_numbers(text):\n",
    "    text=text.replace('\\n', ' ')\n",
    "    text=text.replace('<p>', ' ')\n",
    "    text=text.replace('</p>', ' ')\n",
    "    text=text.translate(str.maketrans('', '','0123456789'))\n",
    "    #text=re.sub(r'[^\\w]', ' ', text)\n",
    "    #print(\"symbols done\")\n",
    "    return text\n",
    "\n",
    "#removes html tags\n",
    "def remove_html(text,flag):\n",
    "    if (flag=='body'):\n",
    "        text=BeautifulSoup(text,'html.parser')\n",
    "    return text\n",
    "\n",
    "#Remove contracted forms of words in statements\n",
    "def decontract(text):\n",
    "    for word in text.split():\n",
    "        if word.lower() in contractions:\n",
    "            text = text.replace(word, contractions[word.lower()])\n",
    "    #print(\"decontraction done\")\n",
    "    return text\n",
    "\n",
    "#Remove punctuations from statements\n",
    "def remove_punctuations(text):\n",
    "    text=text.translate(str.maketrans(string.punctuation,' '*32))\n",
    "    #print(\"punctuations done\")\n",
    "    return text\n",
    "\n",
    "#Remove frequently occurring helping words i.e stopwords\n",
    "stop = stopwords.words('english')\n",
    "def remove_stopwords(text):\n",
    "    text=' '.join([word for word in text.split() if word not in (stop)])\n",
    "    #print(\"stopwords done\")\n",
    "    return text\n",
    "\n",
    "#lemmatizatiiiion\n",
    "def lemmatize(text):\n",
    "    text=' '.join([stemmer.stem(y) for y in text.split()])\n",
    "    #print(\"lemmatization done\")\n",
    "    return text\n",
    "\n",
    "#Tex preprocessing function\n",
    "def text_preprocessing(text,flag):\n",
    "    symbol_free_text=remove_symbols_numbers(text)\n",
    "    html_free_text=remove_html(text,flag)\n",
    "    contraction_free_text=decontract(symbol_free_text)\n",
    "    punctuation_free_text=remove_punctuations(contraction_free_text)\n",
    "    sw_free_text=remove_stopwords(punctuation_free_text)\n",
    "    preprocessed_text=lemmatize(sw_free_text)\n",
    "    return preprocessed_text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#answers['ans_body']=answers['ans_body'].apply(lambda x : text_preprocessing(x.lower()))\n",
    "questions['ques_body']=questions['ques_body'].apply(lambda x : text_preprocessing(x.lower(),\"body\"))\n",
    "questions['ques_title']=questions['ques_title'].apply(lambda x : text_preprocessing(x.lower(),\"title\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "questions.to_csv(\"data_stackOverflow/text_preprocessed_data.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "questions=pd.read_csv(\"data_stackOverflow/text_preprocessed_data.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>ans_score</th>\n",
       "      <th>ans_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>92</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;p&gt;&lt;a href=\"http://svnbook.red-bean.com/\"&gt;Vers...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  ans_score                                           ans_body\n",
       "0  92         13  <p><a href=\"http://svnbook.red-bean.com/\">Vers..."
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "answers.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>ques_score</th>\n",
       "      <th>ques_title</th>\n",
       "      <th>ques_body</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "      <td>sqlstatement execut multipl queri one statement</td>\n",
       "      <td>i written databas generat script href http en ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  ques_score                                       ques_title  \\\n",
       "0  80          26  sqlstatement execut multipl queri one statement   \n",
       "\n",
       "                                           ques_body  \n",
       "0  i written databas generat script href http en ...  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1264216\n",
       "mean           1\n",
       "std           13\n",
       "min          -73\n",
       "25%            0\n",
       "50%            0\n",
       "75%            1\n",
       "max         5190\n",
       "Name: ques_score, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions[\"ques_score\"].describe().apply(lambda x: int(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "bad       670159\n",
       "good      313015\n",
       "medium    281042\n",
       "Name: ques_score_bin, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions['ques_score_bin'] = pd.qcut(questions['ques_score'],labels=[\"bad\", \"medium\", \"good\"], q=3,duplicates= 'drop')\n",
    "questions['ques_score_bin'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "questions=pd.get_dummies(questions, columns=['ques_score_bin'], prefix='ques_score')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>ques_score</th>\n",
       "      <th>ques_title</th>\n",
       "      <th>ques_body</th>\n",
       "      <th>ques_score_bad</th>\n",
       "      <th>ques_score_medium</th>\n",
       "      <th>ques_score_good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "      <td>sqlstatement execut multipl queri one statement</td>\n",
       "      <td>i written databas generat script href http en ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  ques_score                                       ques_title  \\\n",
       "0  80          26  sqlstatement execut multipl queri one statement   \n",
       "\n",
       "                                           ques_body  ques_score_bad  \\\n",
       "0  i written databas generat script href http en ...               0   \n",
       "\n",
       "   ques_score_medium  ques_score_good  \n",
       "0                  0                1  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3750994 entries, 0 to 3750993\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Dtype \n",
      "---  ------  ----- \n",
      " 0   Id      int64 \n",
      " 1   Tag     object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 57.2+ MB\n"
     ]
    }
   ],
   "source": [
    "tags.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array(['flex', 'actionscript-3', 'air', ..., 'union-types', 'voce',\n",
       "        'docker-windows'], dtype=object),\n",
       " 37035)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags['Tag'].unique(),len(tags['Tag'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "javascript                 124155\n",
       "java                       115212\n",
       "c#                         101186\n",
       "php                         98808\n",
       "android                     90659\n",
       "                            ...  \n",
       "escalante                       1\n",
       "anonymousidentification         1\n",
       "uppaal                          1\n",
       "sql-server-2012-web             1\n",
       "creation-pattern                1\n",
       "Name: Tag, Length: 37034, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags['Tag'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "tags_final = pd.DataFrame() \n",
    "tags_final=tags.apply(lambda x: x.mask(x.map(x.value_counts())<np.percentile(tags['Tag'].value_counts().values,99.95), 'other'))\n",
    "tags_final['Id']=tags['Id']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "other            2651071\n",
       "javascript        124155\n",
       "java              115212\n",
       "c#                101186\n",
       "php                98808\n",
       "android            90659\n",
       "jquery             78542\n",
       "python             64601\n",
       "html               58976\n",
       "c++                47591\n",
       "ios                47009\n",
       "mysql              42464\n",
       "css                42308\n",
       "sql                35782\n",
       "asp.net            29970\n",
       "objective-c        26922\n",
       "ruby-on-rails      25789\n",
       ".net               24059\n",
       "c                  23238\n",
       "iphone             21539\n",
       "Name: Tag, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags_final[\"Tag\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "tags_final_one_hot=pd.get_dummies(tags_final, columns=['Tag'], prefix='tag')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>tag_c++</th>\n",
       "      <th>tag_css</th>\n",
       "      <th>tag_html</th>\n",
       "      <th>tag_ios</th>\n",
       "      <th>...</th>\n",
       "      <th>tag_java</th>\n",
       "      <th>tag_javascript</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Id  tag_.net  tag_android  tag_asp.net  tag_c  tag_c#  tag_c++  tag_css  \\\n",
       "0  80         0            0            0      0       0        0        0   \n",
       "1  80         0            0            0      0       0        0        0   \n",
       "\n",
       "   tag_html  tag_ios  ...  tag_java  tag_javascript  tag_jquery  tag_mysql  \\\n",
       "0         0        0  ...         0               0           0          0   \n",
       "1         0        0  ...         0               0           0          0   \n",
       "\n",
       "   tag_objective-c  tag_other  tag_php  tag_python  tag_ruby-on-rails  tag_sql  \n",
       "0                0          1        0           0                  0        0  \n",
       "1                0          1        0           0                  0        0  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags_final_one_hot.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>tag_c++</th>\n",
       "      <th>tag_css</th>\n",
       "      <th>tag_html</th>\n",
       "      <th>tag_ios</th>\n",
       "      <th>tag_iphone</th>\n",
       "      <th>...</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "      <th>maintag</th>\n",
       "      <th>all_tags</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>other/other/other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>other/other/other/other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    tag_.net  tag_android  tag_asp.net  tag_c  tag_c#  tag_c++  tag_css  \\\n",
       "Id                                                                        \n",
       "80         0            0            0      0       0        0        0   \n",
       "90         0            0            0      0       0        0        0   \n",
       "\n",
       "    tag_html  tag_ios  tag_iphone  ...  tag_jquery  tag_mysql  \\\n",
       "Id                                 ...                          \n",
       "80         0        0           0  ...           0          0   \n",
       "90         0        0           0  ...           0          0   \n",
       "\n",
       "    tag_objective-c  tag_other  tag_php  tag_python  tag_ruby-on-rails  \\\n",
       "Id                                                                       \n",
       "80                0          1        0           0                  0   \n",
       "90                0          1        0           0                  0   \n",
       "\n",
       "    tag_sql  maintag                 all_tags  \n",
       "Id                                             \n",
       "80        0    other        other/other/other  \n",
       "90        0    other  other/other/other/other  \n",
       "\n",
       "[2 rows x 22 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tags_final['Tag']=tags_final['Tag'].astype(str)\n",
    "grouped_tags_1 = tags_final_one_hot.groupby(\"Id\").first()\n",
    "grouped_tags_1['maintag']=tags_final.groupby(\"Id\")[\"Tag\"].first()\n",
    "grouped_tags_1['all_tags']=tags_final.groupby(\"Id\")['Tag'].apply(lambda tags: '/'.join(tags))\n",
    "grouped_tags_1.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>tag_c++</th>\n",
       "      <th>tag_css</th>\n",
       "      <th>tag_html</th>\n",
       "      <th>tag_ios</th>\n",
       "      <th>tag_iphone</th>\n",
       "      <th>tag_java</th>\n",
       "      <th>tag_javascript</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    tag_.net  tag_android  tag_asp.net  tag_c  tag_c#  tag_c++  tag_css  \\\n",
       "Id                                                                        \n",
       "80         0            0            0      0       0        0        0   \n",
       "90         0            0            0      0       0        0        0   \n",
       "\n",
       "    tag_html  tag_ios  tag_iphone  tag_java  tag_javascript  tag_jquery  \\\n",
       "Id                                                                        \n",
       "80         0        0           0         0               0           0   \n",
       "90         0        0           0         0               0           0   \n",
       "\n",
       "    tag_mysql  tag_objective-c  tag_other  tag_php  tag_python  \\\n",
       "Id                                                               \n",
       "80          0                0          3        0           0   \n",
       "90          0                0          4        0           0   \n",
       "\n",
       "    tag_ruby-on-rails  tag_sql  \n",
       "Id                              \n",
       "80                  0        0  \n",
       "90                  0        0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_tags_2 = tags_final_one_hot.groupby(\"Id\").sum()\n",
    "grouped_tags_2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_tags_2[grouped_tags_2 > 1] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>tag_c++</th>\n",
       "      <th>tag_css</th>\n",
       "      <th>tag_html</th>\n",
       "      <th>tag_ios</th>\n",
       "      <th>tag_iphone</th>\n",
       "      <th>tag_java</th>\n",
       "      <th>tag_javascript</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    tag_.net  tag_android  tag_asp.net  tag_c  tag_c#  tag_c++  tag_css  \\\n",
       "Id                                                                        \n",
       "80         0            0            0      0       0        0        0   \n",
       "90         0            0            0      0       0        0        0   \n",
       "\n",
       "    tag_html  tag_ios  tag_iphone  tag_java  tag_javascript  tag_jquery  \\\n",
       "Id                                                                        \n",
       "80         0        0           0         0               0           0   \n",
       "90         0        0           0         0               0           0   \n",
       "\n",
       "    tag_mysql  tag_objective-c  tag_other  tag_php  tag_python  \\\n",
       "Id                                                               \n",
       "80          0                0          1        0           0   \n",
       "90          0                0          1        0           0   \n",
       "\n",
       "    tag_ruby-on-rails  tag_sql  \n",
       "Id                              \n",
       "80                  0        0  \n",
       "90                  0        0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_tags_2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1264216, 22), (1264216, 20))"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_tags_1.shape,grouped_tags_2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = questions.merge(grouped_tags_1, on='Id')\n",
    "df_2 = questions.merge(grouped_tags_2, on='Id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.drop([\"Id\",\"ques_score\"],axis=1,inplace=True)\n",
    "df_2.drop([\"Id\",\"ques_score\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_1 shape =  (1264216, 27)\n",
      "df_2 shape =  (1264216, 25)\n"
     ]
    }
   ],
   "source": [
    "print(\"df_1 shape = \",df_1.shape)\n",
    "print(\"df_2 shape = \",df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ques_title</th>\n",
       "      <th>ques_body</th>\n",
       "      <th>ques_score_bad</th>\n",
       "      <th>ques_score_medium</th>\n",
       "      <th>ques_score_good</th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>...</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "      <th>maintag</th>\n",
       "      <th>all_tags</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sqlstatement execut multipl queri one statement</td>\n",
       "      <td>i written databas generat script href http en ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>other/other/other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>good branch merg tutori tortoisesvn</td>\n",
       "      <td>realli good tutori explain href http svnbook r...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>other</td>\n",
       "      <td>other/other/other/other</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        ques_title  \\\n",
       "0  sqlstatement execut multipl queri one statement   \n",
       "1              good branch merg tutori tortoisesvn   \n",
       "\n",
       "                                           ques_body  ques_score_bad  \\\n",
       "0  i written databas generat script href http en ...               0   \n",
       "1  realli good tutori explain href http svnbook r...               0   \n",
       "\n",
       "   ques_score_medium  ques_score_good  tag_.net  tag_android  tag_asp.net  \\\n",
       "0                  0                1         0            0            0   \n",
       "1                  0                1         0            0            0   \n",
       "\n",
       "   tag_c  tag_c#  ...  tag_jquery  tag_mysql  tag_objective-c  tag_other  \\\n",
       "0      0       0  ...           0          0                0          1   \n",
       "1      0       0  ...           0          0                0          1   \n",
       "\n",
       "   tag_php  tag_python  tag_ruby-on-rails  tag_sql  maintag  \\\n",
       "0        0           0                  0        0    other   \n",
       "1        0           0                  0        0    other   \n",
       "\n",
       "                  all_tags  \n",
       "0        other/other/other  \n",
       "1  other/other/other/other  \n",
       "\n",
       "[2 rows x 27 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ques_title</th>\n",
       "      <th>ques_body</th>\n",
       "      <th>ques_score_bad</th>\n",
       "      <th>ques_score_medium</th>\n",
       "      <th>ques_score_good</th>\n",
       "      <th>tag_.net</th>\n",
       "      <th>tag_android</th>\n",
       "      <th>tag_asp.net</th>\n",
       "      <th>tag_c</th>\n",
       "      <th>tag_c#</th>\n",
       "      <th>...</th>\n",
       "      <th>tag_java</th>\n",
       "      <th>tag_javascript</th>\n",
       "      <th>tag_jquery</th>\n",
       "      <th>tag_mysql</th>\n",
       "      <th>tag_objective-c</th>\n",
       "      <th>tag_other</th>\n",
       "      <th>tag_php</th>\n",
       "      <th>tag_python</th>\n",
       "      <th>tag_ruby-on-rails</th>\n",
       "      <th>tag_sql</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sqlstatement execut multipl queri one statement</td>\n",
       "      <td>i written databas generat script href http en ...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>good branch merg tutori tortoisesvn</td>\n",
       "      <td>realli good tutori explain href http svnbook r...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        ques_title  \\\n",
       "0  sqlstatement execut multipl queri one statement   \n",
       "1              good branch merg tutori tortoisesvn   \n",
       "\n",
       "                                           ques_body  ques_score_bad  \\\n",
       "0  i written databas generat script href http en ...               0   \n",
       "1  realli good tutori explain href http svnbook r...               0   \n",
       "\n",
       "   ques_score_medium  ques_score_good  tag_.net  tag_android  tag_asp.net  \\\n",
       "0                  0                1         0            0            0   \n",
       "1                  0                1         0            0            0   \n",
       "\n",
       "   tag_c  tag_c#  ...  tag_java  tag_javascript  tag_jquery  tag_mysql  \\\n",
       "0      0       0  ...         0               0           0          0   \n",
       "1      0       0  ...         0               0           0          0   \n",
       "\n",
       "   tag_objective-c  tag_other  tag_php  tag_python  tag_ruby-on-rails  tag_sql  \n",
       "0                0          1        0           0                  0        0  \n",
       "1                0          1        0           0                  0        0  \n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "other            424442\n",
       "javascript       118826\n",
       "java             114208\n",
       "c#               100192\n",
       "php               92015\n",
       "android           71963\n",
       "python            62622\n",
       "c++               45318\n",
       "ios               36618\n",
       "jquery            31429\n",
       "ruby-on-rails     22556\n",
       "html              22276\n",
       "sql               22094\n",
       "mysql             20254\n",
       "c                 18987\n",
       "iphone            17165\n",
       "asp.net           13329\n",
       "css               11288\n",
       "objective-c       10055\n",
       ".net               8546\n",
       "nan                  33\n",
       "Name: maintag, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1['maintag'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Removing numbers has led to this kind of text which ignores the version of the framework"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('What are the useful new ASP.NET features in the .NET Framework 3.5?',\n",
       " 'use new asp net featur net framework')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_original.iloc[100]['Title'],df_2.iloc[100]['ques_title']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# All negations have been removed and thus meaning of sentence has changed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(\"<p>I've kept up to date with new features in the C# language as it's moved from version 1 through version 3.  I haven't done such a good job keeping up to date with ASP.NET.  I feel like some of the post version 1 features are not so good (e.g. the AJAX framework) or are just not that useful to me (e.g. the membership framework).  Can anyone recommend any new killer ASP.NET features that might have gone unnoticed?</p>\\n\",\n",
       " 'i kept date new featur c languag move version version done good job keep date asp net feel like post version featur good e g ajax framework use e g membership framework anyon recommend new killer asp net featur might gone unnot')"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "questions_original.iloc[100]['Body'],df_1.iloc[100]['ques_body']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate entries: 4\n",
      "Dupplicate entries: 5\n"
     ]
    }
   ],
   "source": [
    "#2 duplicates present\n",
    "print('Duplicate entries: {}'.format(df_1.duplicated().sum()))\n",
    "print('Dupplicate entries: {}'.format(df_2.duplicated().sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "#duplicates dropped\n",
    "df_1.drop_duplicates( keep='first', inplace=True)\n",
    "df_2.drop_duplicates( keep='first', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_1 shape =  (1264212, 27)\n",
      "df_2 shape =  (1264211, 25)\n"
     ]
    }
   ],
   "source": [
    "print(\"df_1 shape = \",df_1.shape)\n",
    "print(\"df_2 shape = \",df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate entries: 0\n",
      "Dupplicate entries: 0\n"
     ]
    }
   ],
   "source": [
    "print('Duplicate entries: {}'.format(df_1.duplicated().sum()))\n",
    "print('Dupplicate entries: {}'.format(df_2.duplicated().sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1.to_csv(\"data_stackOverflow/final_dataframe_with_1tag_onehot.csv\")\n",
    "df_2.to_csv(\"data_stackOverflow/final_dataframe_with_multipletags_onehot.csv\")\n",
    "#answers.to_csv(\"data_stackOverflow/preprocessed_answers.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ques_title           15\n",
      "ques_body             1\n",
      "ques_score_bad        0\n",
      "ques_score_medium     0\n",
      "ques_score_good       0\n",
      "tag_.net              0\n",
      "tag_android           0\n",
      "tag_asp.net           0\n",
      "tag_c                 0\n",
      "tag_c#                0\n",
      "tag_c++               0\n",
      "tag_css               0\n",
      "tag_html              0\n",
      "tag_ios               0\n",
      "tag_iphone            0\n",
      "tag_java              0\n",
      "tag_javascript        0\n",
      "tag_jquery            0\n",
      "tag_mysql             0\n",
      "tag_objective-c       0\n",
      "tag_other             0\n",
      "tag_php               0\n",
      "tag_python            0\n",
      "tag_ruby-on-rails     0\n",
      "tag_sql               0\n",
      "maintag               0\n",
      "all_tags              0\n",
      "dtype: int64\n",
      "ques_title           15\n",
      "ques_body             1\n",
      "ques_score_bad        0\n",
      "ques_score_medium     0\n",
      "ques_score_good       0\n",
      "tag_.net              0\n",
      "tag_android           0\n",
      "tag_asp.net           0\n",
      "tag_c                 0\n",
      "tag_c#                0\n",
      "tag_c++               0\n",
      "tag_css               0\n",
      "tag_html              0\n",
      "tag_ios               0\n",
      "tag_iphone            0\n",
      "tag_java              0\n",
      "tag_javascript        0\n",
      "tag_jquery            0\n",
      "tag_mysql             0\n",
      "tag_objective-c       0\n",
      "tag_other             0\n",
      "tag_php               0\n",
      "tag_python            0\n",
      "tag_ruby-on-rails     0\n",
      "tag_sql               0\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# No missing value present \n",
    "print(df_1.isnull().sum())\n",
    "print(df_2.isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n"
     ]
    }
   ],
   "source": [
    "print(df_1['maintag'].isnull().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
